package com.bite.mybatis.mapper;

import com.bite.mybatis.model.UserInfo;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface UserInfoMapper {

    @ResultMap("BaseMap")
    @Select("select * from userinfo where id=1")
    List<UserInfo> queryUserInfos();

    @Select("select * from userinfo where id = #{value}")
    UserInfo queryUserInfo(@Param("id") Integer id);

    @Select("select * from userinfo where id = #{id} and userName =#{name}")
    UserInfo queryUserInfoByIdAndName(Integer id,String name);

    @Select("select * from userinfo where id = #{id} and userName =#{name}")
    UserInfo queryUserInfoByIdAndName2(@Param("id1") Integer id,@Param("name1") String name);

    @Insert("insert into userinfo (username,password,age,gender,phone) " +
            "value(#{userName},#{password},#{age},#{gender},#{phone})")
    Integer insert (UserInfo userInfo);

    @Options(useGeneratedKeys = true,keyProperty = "id")
    @Insert("insert into userinfo (username,password,age,gender,phone) " +
            "value(#{userInfo.userName},#{userInfo.password},#{userInfo.age}," +
            "#{userInfo.gender},#{userInfo.phone})")
    Integer insert2 (@Param("userInfo") UserInfo userInfo);

    @Delete("delete from userinfo where id = #{id}")
    Integer delete(Integer id);

    @Update("update userinfo set password = #{password} where id = #{id}")
    Integer update(UserInfo userInfo);

    /**
     * 起别名
     * @return
     */
    @Select("select id,username,password,age,gender,phone," +
            "delete_flag as deleteFlag,create_time as createTime," +
            "update_time as updateTime from userinfo where id=1")
    List<UserInfo> queryUserInfos2();

    /**
     * 注解
     * @return
     */
    @Results(id="BaseMap",value = {
            @Result(column = "delete_flag",property = "deleteFlag"),
            @Result(column = "create_time",property = "createTime"),
            @Result(column = "update_time",property = "updateTime"),

    })
    @Select("select id,username,password,age,gender,phone," +
            "delete_flag as deleteFlag,create_time as createTime,update_time as updateTime from userinfo where id=1")
    List<UserInfo> queryUserInfos3();

    @Select("select * from userinfo where id=1")
    List<UserInfo> queryUserInfos4();

    //演示SQL注入
    @Select("select * from userinfo where username=#{username} and password =#{password}")
    List<UserInfo> queryUserByNameAndPassword(String username,String password);

    @Select("select * from userinfo where username='${username}' and password ='${password}'")
    List<UserInfo> queryUserByNameAndPassword2(String username,String password);

    //排序
    @Select("select * from userinfo order by id ${order}")
    List<UserInfo> queryUserInfoByOrder(String order);

    @Select("select * from userinfo where username like CONCAT('%','min','%')")
    List<UserInfo> queryUserInfoByLike(String name);

    @Insert("<script>insert into userinfo (username,password," +
            "<if test='age!=null'> age,</if> " +
            "gender,phone) " +
            "values (#{username},#{password}, " +
            "<if test=\"age!=null\"> #{age},</if> " +
            "#{gender},#{phone}) " +
            "</script>")
    Integer insertByCondition(UserInfo userInfo);
}
